﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using PeachLite.Entity;


namespace PeachLite.Helpers
{
    /// <summary>
    /// 用于访问数据库，执行存储过程的静态类
    /// </summary>
    public static class SqlHelper
    {
        //public static string ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        private static string ConnectionString = @"Data Source=pro4;Initial Catalog=PeachLite;User ID=sa;Password=letmego";

        /// <summary>
        /// 执行SQL命令，返回一个系统结果对象
        /// </summary>
        /// <param name="sql">SQL命令</param>
        /// <returns>执行结果</returns>
        public static SysResult Execute(string sql)
        {
            SysResult result = new SysResult();

            SqlDataAdapter da = new SqlDataAdapter(sql, ConnectionString);
            DataSet ds = new DataSet();

            try
            {
                da.Fill(ds);

                if (ds.Tables.Count > 0)
                {
                    result.Code = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                    result.Success = result.Code == 0;
                    result.Message = ds.Tables[0].Rows[0][1].ToString();

                    for (int i = 2; i < ds.Tables[0].Columns.Count; i++)
                    {
                        result.Data.Add(ds.Tables[0].Columns[i].ToString(), ds.Tables[0].Rows[0][i].ToString());
                    }

                    if (ds.Tables.Count > 1)
                    {
                        //移除第一个结果集
                        ds.Tables.RemoveAt(0);
                    }

                    result.DSet = ds;
                }
                else
                {
                    result.Code = 1000;
                    result.Success = false;
                    result.Message = "数据访问异常:执行结果为空.";
                    return result;
                }
            }
            catch (Exception ex)
            {
                result.Code = 1001;
                result.Success = false;
                result.Message = "系统异常:" + ex.Message;
                result.Exception = ex;
            }

            return result;
        }
    }
}